﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;
using System.Threading;

/// <summary>
/// Descripción breve de Persistencia
/// </summary>
public class Persistencia
{
    private static Persistencia p;

    private MySqlConnection Conexion;
    private MySqlDataReader Reader;
    private MySqlCommand SQL;
    private MySqlDataAdapter sqlAdpt;
    private DataSet dSet;
    private string Query;

    public Persistencia()
    {
        this.Conexion = new MySqlConnection();
        Conexion.ConnectionString =
            "Server = db4free.net;" +//25.233.45.234; 
            "Port = 3306; " +
            "Database = cbc12345;" +
            "Uid = cbc017;" +
            "Pwd = ludacris;";//p3nc11$;"; //
    }
    #region ------------------------------- OPERATOR'S -------------------------------
    public static Persistencia getInstance()
    {
        if (p == null) { p = new Persistencia(); }
        return p;
    }
    private void Open()
    {
        if (Conexion.State == System.Data.ConnectionState.Open)
        {
            Thread.Sleep(1000);
            Close();
        }
        Conexion.Open();
    }
    private void Close()
    {
        Conexion.Close();
    }

    #endregion

    #region ------------------------------- SELECT'S -------------------------------
    public ArrayList ListaRubro()
    {
        ArrayList Lista = new ArrayList();

        Query = "SELECT *" +
                "FROM _RUBRO";

        SQL = new MySqlCommand(Query, Conexion);
        //SQL.Parameters.AddWithValue("Tipo", Tipo);

        DataSet dSet = new DataSet();
        Open();
        sqlAdpt = new MySqlDataAdapter();
        sqlAdpt.SelectCommand = SQL;
        sqlAdpt.Fill(dSet);
        foreach (DataRow Row in dSet.Tables[0].Rows)
        {
            
            int id = Convert.ToInt32(Row["id_rubro"].ToString());
            string nombre = Row["nombre"].ToString();
            string descripcion = Convert.ToString(Row["descripcion"]);
            DateTime fecha = DateTime.Now;

            cRubro nuevo = new cRubro(id,nombre,descripcion,fecha,1);
            Lista.Add(nuevo);

        }

        Close();
        return Lista;
    }


    public ArrayList ListaComuna()
    {
        ArrayList Lista = new ArrayList();

        Query = "SELECT * " +
                "FROM comuna";

        SQL = new MySqlCommand(Query, Conexion);
        //SQL.Parameters.AddWithValue("Tipo", Tipo);

        DataSet dSet = new DataSet();
        Open();
        sqlAdpt = new MySqlDataAdapter();
        sqlAdpt.SelectCommand = SQL;
        sqlAdpt.Fill(dSet);
        foreach (DataRow Row in dSet.Tables[0].Rows)
        {

            int id = Convert.ToInt32(Row["co_id"].ToString());
            int id_provincia = Convert.ToInt32(Row["co_pr"].ToString());
            string nombre_comuna= Row["co_descripcion"].ToString();


            cComuna nueva = new cComuna(id,id_provincia,nombre_comuna);
            Lista.Add(nueva);

        }

        Close();
        return Lista;
    }
    #endregion 
}